﻿using Common;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Xml;

/// MySQLDataControl 帮助类
/// <summary>
/// MySQLDataControl 帮助类
/// </summary>
public class MySQLDataControl
{
    public MySQLDataControl()
    {
        //
        // TODO: 在此处添加构造函数逻辑
        //
    }

    public static string connectionString;
    public static string IP = CheckInfo.GetClientIP();

    /// 获取链接字符串
    /// <summary>
    /// 获取链接字符串
    /// </summary>
    /// <returns></returns>
    public static string GetConnection()
    {
        if (CheckInfo.IsLocal()) connectionString = ConfigurationManager.ConnectionStrings["MySqlConnLocal"].ToString();
        else connectionString = ConfigurationManager.ConnectionStrings["MySqlConn"].ToString();
        return connectionString;
    }

    /// 获得数据库链接字符串
    /// <summary>
    /// 获得数据库链接字符串
    /// </summary>
    /// <param name="connName"></param>
    /// <returns></returns>
    public static string GetConnection(string connName)
    {
        if (CheckInfo.IsLocal()) connectionString = ConfigurationManager.ConnectionStrings[connName + "Local"].ToString();
        else connectionString = ConfigurationManager.ConnectionStrings[connName].ToString();
        return connectionString;
    }


    public static DataTable GetDataTable(string sqlStr, string connName, params MySqlParameter[] vPar)
    {
        DataTable result = null;
        if (CheckInfo.IsLocal()) connectionString = ConfigurationManager.ConnectionStrings[connName + "Local"].ToString();
        else connectionString = ConfigurationManager.ConnectionStrings[connName].ToString();
        using (MySqlConnection sqlConnection = new MySqlConnection(connectionString))
        {
            sqlConnection.Open();
            result = ExecuteDataTable(sqlConnection, CommandType.Text, sqlStr, vPar);
        }
        return result;
    }

    public static DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText)
    {
        return ExecuteDataTable(connectionString, commandType, commandText, null);
    }

    public static DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
    {
        DataTable result;
        using (MySqlConnection sqlConnection = new MySqlConnection(connectionString))
        {
            sqlConnection.Open();
            result = ExecuteDataTable(sqlConnection, commandType, commandText, commandParameters);
        }
        return result;
    }

    public static DataTable ExecuteDataTable(MySqlTransaction transaction, CommandType commandType, string commandText)
    {
        return ExecuteDataTable(transaction, commandType, commandText, null);
    }


    public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)
    {
        return ExecuteNonQuery(connectionString, commandType, commandText, null);
    }


    public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
    {
        int result;
        using (MySqlConnection sqlConnection = new MySqlConnection(connectionString))
        {
            sqlConnection.Open();
            result = ExecuteNonQuery(sqlConnection, commandType, commandText, commandParameters);
        }
        return result;
    }

    public static int ExecuteNonQuery(MySqlTransaction transaction, CommandType commandType, string commandText)
    {
        return ExecuteNonQuery(transaction, commandType, commandText, null);
    }

    public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText)
    {
        return ExecuteScalar(connectionString, commandType, commandText, null);
    }

    public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
    {
        object result;
        using (MySqlConnection sqlConnection = new MySqlConnection(connectionString))
        {
            sqlConnection.Open();
            result = ExecuteScalar(sqlConnection, commandType, commandText, commandParameters);
        }
        return result;
    }

    public static object ExecuteScalar(MySqlConnection connection, CommandType commandType, string commandText)
    {
        return ExecuteScalar(connection, commandType, commandText, null);
    }

    public static object ExecuteScalar(MySqlTransaction transaction, CommandType commandType, string commandText)
    {
        return ExecuteScalar(transaction, commandType, commandText, null);
    }

    public static MySqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)
    {
        return ExecuteReader(connectionString, commandType, commandText, null);
    }
    public static MySqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
    {
        MySqlDataReader result;
        using (MySqlConnection sqlConnection = new MySqlConnection(connectionString))
        {
            sqlConnection.Open();
            try
            {
                result = ExecuteReader(sqlConnection, null, commandType, commandText, commandParameters, SqlConnectionOwnership.Internal);
            }
            catch
            {
                sqlConnection.Close();
                throw;
            }
        }
        return result;
    }

    public static MySqlDataReader ExecuteReader(MySqlConnection connection, CommandType commandType, string commandText)
    {
        return ExecuteReader(connection, commandType, commandText, null);
    }

    public static MySqlDataReader ExecuteReader(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
    {
        return ExecuteReader(connection, null, commandType, commandText, commandParameters, SqlConnectionOwnership.External);
    }

    public static MySqlDataReader ExecuteReader(MySqlTransaction transaction, CommandType commandType, string commandText)
    {
        return ExecuteReader(transaction, commandType, commandText, null);
    }
    public static MySqlDataReader ExecuteReader(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
    {
        return ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, SqlConnectionOwnership.External);
    }


    #region 私有方法

    public static DataTable ExecuteDataTable(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
    {
        MySqlCommand sqlCommand = new MySqlCommand();
        PrepareCommand(sqlCommand, connection, null, commandType, commandText, commandParameters);
        MySqlDataAdapter sqlDataAdapter = new MySqlDataAdapter(sqlCommand);
        DataTable dt = new DataTable();
        sqlDataAdapter.Fill(dt);
        sqlCommand.Parameters.Clear();
        return dt;
    }

    public static DataTable ExecuteDataTable(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
    {
        MySqlCommand sqlCommand = new MySqlCommand();
        PrepareCommand(sqlCommand, transaction.Connection, transaction, commandType, commandText, commandParameters);
        MySqlDataAdapter sqlDataAdapter = new MySqlDataAdapter(sqlCommand);
        DataTable dataTable = new DataTable();
        sqlDataAdapter.Fill(dataTable);
        sqlCommand.Parameters.Clear();
        return dataTable;
    }



    public static int ExecuteNonQuery(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
    {
        MySqlCommand sqlCommand = new MySqlCommand();
        PrepareCommand(sqlCommand, connection, null, commandType, commandText, commandParameters);
        int result = sqlCommand.ExecuteNonQuery();
        sqlCommand.Parameters.Clear();
        return result;
    }

    public static int ExecuteNonQuery(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
    {
        MySqlCommand sqlCommand = new MySqlCommand();
        PrepareCommand(sqlCommand, transaction.Connection, transaction, commandType, commandText, commandParameters);
        int result = sqlCommand.ExecuteNonQuery();
        sqlCommand.Parameters.Clear();
        return result;
    }


    public static object ExecuteScalar(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
    {
        MySqlCommand sqlCommand = new MySqlCommand();
        PrepareCommand(sqlCommand, connection, null, commandType, commandText, commandParameters);
        object result = sqlCommand.ExecuteScalar();
        sqlCommand.Parameters.Clear();
        return result;
    }
    public static object ExecuteScalar(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
    {
        MySqlCommand sqlCommand = new MySqlCommand();
        PrepareCommand(sqlCommand, transaction.Connection, transaction, commandType, commandText, commandParameters);
        object result = sqlCommand.ExecuteScalar();
        sqlCommand.Parameters.Clear();
        return result;
    }

    public static MySqlDataReader ExecuteReader(MySqlConnection connection, MySqlTransaction transaction, CommandType commandType, string commandText, MySqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
    {
        MySqlCommand sqlCommand = new MySqlCommand();
        PrepareCommand(sqlCommand, connection, transaction, commandType, commandText, commandParameters);
        MySqlDataReader result;
        if (connectionOwnership == SqlConnectionOwnership.External)
        {
            result = sqlCommand.ExecuteReader();
        }
        else
        {
            result = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
        }
        return result;
    }


    private static void PrepareCommand(MySqlCommand command, MySqlConnection connection, MySqlTransaction transaction, CommandType commandType, string commandText, MySqlParameter[] commandParameters)
    {
        if (connection.State != ConnectionState.Open)
        {
            connection.Open();
        }
        command.Connection = connection;
        command.CommandText = commandText;
        if (transaction != null)
        {
            command.Transaction = transaction;
        }
        command.CommandType = commandType;
        if (commandParameters != null)
        {
            AttachParameters(command, commandParameters);
        }

    }

    /// 参数
    /// <summary>
    /// 参数
    /// </summary>
    /// <param name="command"></param>
    /// <param name="commandParameters"></param>
    private static void AttachParameters(MySqlCommand command, MySqlParameter[] commandParameters)
    {
        for (int i = 0; i < commandParameters.Length; i++)
        {
            MySqlParameter sqlParameter = commandParameters[i];
            if (sqlParameter.Direction == ParameterDirection.InputOutput && sqlParameter.Value == null)
            {
                sqlParameter.Value = DBNull.Value;
            }
            command.Parameters.Add(sqlParameter);
        }
    }

    public enum SqlConnectionOwnership
    {
        Internal,
        External
    }

    #endregion 私有方法

}